﻿using System;
using System.Collections.Generic;
using System.Text;

namespace Pixysoft.SQL.Core
{
    class SQLSelectExpression : ISQLSelectExpression
    {
        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLSelectExpression(SQLStringBuilder builder)
        {
            this.builder = builder;
        }

        public ISQLTableExpression From(params string[] tables)
        {
            builder.AppendBlank();

            builder.Append("FROM");

            builder.Push();

            builder.AppendBlank();

            foreach (string table in tables)
                builder.Append("{0},", table);

            builder.Remove(builder.Length - 1, 1);

            return new SQLTableExpression(builder);
        }
    }

    class SQLTableExpression : SQLEndCreator, ISQLTableExpression
    {
        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLTableExpression(SQLStringBuilder builder)
            : base(builder)
        {
            this.builder = builder;
        }

        public ISQLTableExpression InnerJoin(string table)
        {
            if (builder.IndentCounter > 0)
            {
                builder.AppendEndBrace();
            }

            builder.Indent();//缩进一次

            builder.AppendBlank();

            builder.Append("INNER JOIN");

            builder.AppendBlank();

            builder.Append(table);

            return new SQLTableExpression(builder);
        }

        public ISQLTableExpression Join(string table)
        {
            if (builder.IndentCounter > 0)
            {
                builder.AppendEndBrace();
            }

            builder.Indent();//缩进一次

            builder.AppendBlank();

            builder.Append("JOIN");

            builder.AppendBlank();

            builder.Append(table);

            return new SQLTableExpression(builder);
        }

        public ISQLTableExpression LeftJoin(string table)
        {
            if (builder.IndentCounter > 0)
            {
                builder.AppendEndBrace();
            }

            builder.Indent();//缩进一次

            builder.AppendBlank();

            builder.Append("LEFT JOIN");

            builder.AppendBlank();

            builder.Append(table);

            return new SQLTableExpression(builder);
        }

        public ISQLTableExpression RightJoin(string table)
        {
            if (builder.IndentCounter > 0)
            {
                builder.AppendEndBrace();
            }

            builder.Indent();//缩进一次

            builder.AppendBlank();

            builder.Append("RIGHT JOIN");

            builder.AppendBlank();

            builder.Append(table);

            return new SQLTableExpression(builder);
        }

        public ISQLMiddleExpression On(string column)
        {
            builder.AppendBlank();

            builder.Append("ON");

            builder.AppendBlank();

            builder.Append(column);

            return new SQLMiddleExpression(builder);
        }

        public ISQLExpression Where(string table)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("WHERE");

            builder.AppendBlank();

            builder.Append(table);

            return new SQLExpression(builder);
        }

        public ISQLGroupBy GroupBy(params string[] columns)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("GROUP BY");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLGroupBy(builder);
        }

        public ISQLEndCreator OrderBy(params string[] columns)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("ORDER BY");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLEndCreator(builder);
        }
    }

    class SQLExpression : ISQLExpression
    {
        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLExpression(SQLStringBuilder builder)
        {
            this.builder = builder;
        }

        public ISQLExpressionRight Equal(object column)
        {
            builder.AppendBlank();

            builder.Append("=");

            AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight Less(object column) { return Less(column, false); }

        public ISQLExpressionRight Great(object column) { return Great(column, false); }

        public ISQLExpressionRight LessEqual(object column) { return LessEqual(column, false); }

        public ISQLExpressionRight GreatEqual(object column) { return GreatEqual(column, false); }

        public ISQLExpressionRight NotEqual(object column) { return NotEqual(column, false); }

        public ISQLExpressionRight Like(object column) { return Like(column, false); }

        public ISQLExpressionRight IsNull()
        {
            builder.AppendBlank();

            builder.Append("IS NULL");

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight IsNotNull()
        {
            builder.AppendBlank();

            builder.Append("IS NOT NULL");

            return new SQLExpressionRight(builder);
        }

        public ISQLSelectExpression InSelect(params string[] columns)
        {
            builder.AppendBlank();

            builder.Append("IN");

            builder.Push();

            builder.Indent();

            builder.AppendBlank();

            builder.Append("SELECT");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLSelectExpression(builder);
        }

        public ISQLSelectExpression NotInSelect(params string[] columns)
        {
            builder.AppendBlank();

            builder.Append("NOT IN");

            builder.Push();

            builder.Indent();

            builder.AppendBlank();

            builder.Append("SELECT");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLSelectExpression(builder);
        }

        public ISQLSelectExpression InSelectAll()
        {
            builder.AppendBlank();

            builder.Append("IN");

            builder.Push();

            builder.Indent();

            builder.AppendBlank();

            builder.Append("SELECT *");

            return new SQLSelectExpression(builder);
        }

        public ISQLSelectExpression NotInSelectAll()
        {
            builder.AppendBlank();

            builder.Append("NOT IN");

            builder.Push();

            builder.Indent();

            builder.AppendBlank();

            builder.Append("SELECT *");

            return new SQLSelectExpression(builder);
        }

        private void AppendFactor(object value)
        {
            builder.AppendBlank();

            switch (Pixysoft.Tools.ParserHelper.GetDataType(value))
            {
                case DotNetDataType.String:
                    {
                        string svalue = value.ToString();
                        if (svalue.StartsWith(":"))
                            builder.Append("{0}", svalue);
                        else
                            builder.Append("'{0}'", svalue);
                        break;
                    }
                case DotNetDataType.DateTime:
                    {
                        builder.Append("'{0}'", value.ToString());
                        break;
                    }
                default:
                    {
                        builder.Append(value.ToString());

                        break;
                    }
            }
        }


        public ISQLExpressionRight Equal(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight Less(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight Great(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append(">");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight LessEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight GreatEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append(">=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight NotEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<>");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLExpressionRight(builder);
        }

        public ISQLExpressionRight Like(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("LIKE");

            builder.AppendBlank();

            switch (Pixysoft.Tools.ParserHelper.GetDataType(column))
            {
                case DotNetDataType.String:
                    {
                        string svalue = column.ToString();

                        if (isParameter)
                            svalue = ":" + svalue;

                        if (svalue.StartsWith(":"))
                            builder.Append("{0}", svalue);
                        else
                            builder.Append("'%{0}%'", svalue);
                        break;
                    }
                case DotNetDataType.DateTime:
                    {
                        builder.Append("'%{0}%'", column.ToString());
                        break;
                    }
                default:
                    {
                        builder.Append(column.ToString());

                        break;
                    }
            }

            return new SQLExpressionRight(builder);
        }
    }

    class SQLExpressionRight : SQLEndCreator, ISQLExpressionRight
    {
        int bracecounter = 0;

        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLExpressionRight(SQLStringBuilder builder)
            : base(builder)
        {
            this.builder = builder;
        }

        public ISQLExpression And(string column)
        {
            builder.AppendBlank();

            builder.Append("AND");

            for (int i = 0; i < bracecounter; i++)
            {
                builder.AppendBlank();

                builder.Append("(");
            }

            builder.AppendBlank();

            builder.Append(column);

            return new SQLExpression(builder);
        }

        public ISQLExpression Or(string column)
        {
            builder.AppendBlank();

            builder.Append("OR");

            for (int i = 0; i < bracecounter; i++)
            {
                builder.AppendBlank();

                builder.Append("(");
            }

            builder.AppendBlank();

            builder.Append(column);

            return new SQLExpression(builder);
        }

        public ISQLGroupBy GroupBy(params string[] columns)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("GROUP BY");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLGroupBy(builder);
        }

        public ISQLEndCreator OrderBy(params string[] columns)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("ORDER BY");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLEndCreator(builder);
        }

        public ISQLExpressionRight Brace
        {
            get
            {
                bracecounter++;

                return this;
            }
        }

        public ISQLExpressionRight BraceEnd
        {
            get
            {
                builder.AppendBlank();

                builder.Append(")");

                return this;
            }
        }
    }

    class SQLMiddleExpression : ISQLMiddleExpression
    {
        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLMiddleExpression(SQLStringBuilder builder)
        {
            this.builder = builder;
        }

        public ISQLMiddleExpressionRight Equal(object column) { return Equal(column, false); }

        public ISQLMiddleExpressionRight Less(object column) { return Less(column, false); }

        public ISQLMiddleExpressionRight Great(object column) { return Great(column, false); }

        public ISQLMiddleExpressionRight LessEqual(object column) { return LessEqual(column, false); }

        public ISQLMiddleExpressionRight GreatEqual(object column) { return GreatEqual(column, false); }

        public ISQLMiddleExpressionRight NotEqual(object column) { return NotEqual(column, false); }

        private void AppendFactor(object value)
        {
            builder.AppendBlank();

            switch (Pixysoft.Tools.ParserHelper.GetDataType(value))
            {
                case DotNetDataType.String:
                case DotNetDataType.DateTime:
                default:
                    {
                        builder.Append(value.ToString());

                        break;
                    }
            }
        }


        public ISQLMiddleExpressionRight Equal(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }

        public ISQLMiddleExpressionRight Less(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }

        public ISQLMiddleExpressionRight Great(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append(">");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }

        public ISQLMiddleExpressionRight LessEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }

        public ISQLMiddleExpressionRight GreatEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append(">=");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }

        public ISQLMiddleExpressionRight NotEqual(object column, bool isParameter)
        {
            builder.AppendBlank();

            builder.Append("<>");

            if (isParameter)
                AppendFactor(":" + column);
            else
                AppendFactor(column);

            return new SQLMiddleExpressionRight(builder);
        }
    }

    class SQLMiddleExpressionRight : SQLTableExpression, ISQLMiddleExpressionRight
    {
        int bracecounter = 0;

        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLMiddleExpressionRight(SQLStringBuilder builder)
            : base(builder)
        {
            this.builder = builder;
        }

        public ISQLMiddleExpression And(string column)
        {
            builder.AppendBlank();

            builder.Append("AND");

            for (int i = 0; i < bracecounter; i++)
            {
                builder.AppendBlank();

                builder.Append("(");
            }

            builder.Append(" {0}", column);

            return new SQLMiddleExpression(builder);
        }

        public ISQLMiddleExpression Or(string column)
        {
            builder.AppendBlank();

            builder.Append("OR");

            for (int i = 0; i < bracecounter; i++)
            {
                builder.AppendBlank();

                builder.Append("(");
            }

            builder.Append(" {0}", column);

            return new SQLMiddleExpression(builder);
        }

        public ISQLMiddleExpressionRight Brace
        {
            get
            {
                bracecounter++;

                return this;
            }
        }

        public ISQLMiddleExpressionRight BraceEnd
        {
            get
            {
                builder.AppendBlank();

                builder.Append(")");

                return this;
            }
        }
    }

    class SQLGroupBy : SQLEndCreator, ISQLGroupBy
    {
        SQLStringBuilder builder = new SQLStringBuilder();

        public SQLGroupBy(SQLStringBuilder builder)
            : base(builder)
        {
            this.builder = builder;
        }

        public ISQLEndCreator OrderBy(params string[] columns)
        {
            builder.Pop();

            builder.AppendBlank();

            builder.Append("ORDER BY");

            builder.AppendBlank();

            foreach (string column in columns)
            {
                builder.Append("{0},", column);
            }

            builder.Remove(builder.Length - 1, 1);

            return new SQLEndCreator(builder);
        }
    }
}
